package ligueBaseball;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Equipe {

    private PreparedStatement stmtExiste;
    private PreparedStatement stmtInsert;
    private PreparedStatement stmtMaxId;
    private PreparedStatement stmtTous;
    private PreparedStatement stmtDelete;
    private Connexion cx;

    public Equipe(Connexion cx) throws SQLException {
        this.cx = cx;

        stmtExiste = cx.getConnection().prepareStatement("select equipeid, terrainid, equipenom from equipe where equipenom = ?");
        stmtInsert = cx.getConnection().prepareStatement("insert into equipe (equipeid, terrainid, equipenom) values (?,NULL,?)");
        stmtMaxId = cx.getConnection().prepareStatement("select max(equipeid) from equipe");
        stmtTous = cx.getConnection().prepareStatement("select equipeid, equipenom from equipe");
        stmtDelete = cx.getConnection().prepareStatement("delete from equipe where equipeid = ?");
    }

    public Connexion getCx() {
        return cx;
    }

    public boolean existe(String equipenom) throws SQLException {
        stmtExiste.setString(1, equipenom);
        ResultSet rset = stmtExiste.executeQuery();
        boolean equipeExiste = rset.next();
        rset.close();
        return equipeExiste;
    }

    public TupleEquipe getEquipe(String equipenom) throws SQLException {
        stmtExiste.setString(1, equipenom);
        ResultSet rset = stmtExiste.executeQuery();
        if (rset.next()) {
            TupleEquipe tupleEquipe = new TupleEquipe();
            tupleEquipe.setId(rset.getInt(1));
            tupleEquipe.setTerrainId(rset.getInt(2));
            tupleEquipe.setNom(rset.getString(3));
            return tupleEquipe;
        } else {
            return null;
        }
    }

    public int creer(String equipenom) throws SQLException {

        int id = getMaxId();
        stmtInsert.setInt(1, id);
        stmtInsert.setString(2, equipenom);
        stmtInsert.executeUpdate();
        return id;

    }

    public void supprimer(String equipenom) throws SQLException {
        stmtExiste.setString(1, equipenom);
        ResultSet rset = stmtExiste.executeQuery();
        int equipeid = rset.getInt(1);
        stmtDelete.setInt(1, equipeid);
        stmtDelete.executeUpdate();
    }

    public String afficherTous() throws SQLException {
        ResultSet rset = stmtTous.executeQuery();
        String tous = null;
        if (rset.next()) {
            tous = "La liste d'équipes est la suivante : \n";
            do {
                tous += rset.getInt(1);
                tous += " ";
                tous += rset.getString(2);
                tous += "\n";
            } while (rset.next());
        } else {
            tous = "Il n'y a pas d'équipe dans la liste.";
        }
        return tous;
    }

    public int getEquipeId(String equipenom) throws SQLException {
        stmtExiste.setString(1, equipenom);
        ResultSet rset = stmtExiste.executeQuery();
        rset.next();
        int id = rset.getInt(1);
        rset.close();
        return id;
    }

    public TupleEquipe getEquipe(int equipeId) throws SQLException {
    stmtExiste.setInt(1, equipeId);
        ResultSet rset = stmtExiste.executeQuery();
        TupleEquipe tupleEquipe = new TupleEquipe();
        if (rset.next()) {
            tupleEquipe.setId(equipeId);
            tupleEquipe.setNom(rset.getString(2));
            return tupleEquipe;
        }
        rset.close();
        return null;
    }
    
        private int getMaxId() throws SQLException {
        ResultSet rset = stmtMaxId.executeQuery();
        rset.next();
        int id = rset.getInt(1) + 1;
        rset.close();
        return id;
    }
}
